<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>常用sql语句</title>
    <style type="text/css">
        *{
            font-family:'consolas';
        }
        body{
            margin-bottom:100px;
        }
        a{
            text-decoration:none;
            color:#fff;
        }
        a:hover{
            color: #d90a81
        }
        .category{
            position:fixed;
            /*right:0;*/
            bottom:0;
            background: #000;
            padding:0;
            padding-right:30px;
        }
        .category li{
            float:left;
            margin-left:30px;
            list-style-type:none;
        }
    </style>
</head>
<body>
    <ul class='category'>
        <li>
            <a href="#xiugaishuju">修改数据</a>
        </li>
        <li>
            <a href="#tianjiashuju">添加数据</a>
        </li>
        <li>
            <a href="#shanchuku">删除数据库</a>
        </li>
        <li>
            <a href="#shanchubiao">删除数据表</a>
        </li>
        <li>
            <a href="#qingkongbiao">清空数据表</a>
        </li>
        <li>
            <a href="#shanchushuju">删除数据</a>
        </li>
        <li>
            <a href="#tianjiaziduan">添加字段</a>
        </li>
        <li>
            <a href="#xiugaiziduan">修改字段</a>
        </li>
        <li>
            <a href="#chaxunjiekou">查询结构</a>
        </li>
        <li>
            <a href="#chaxunshuju">查询数据</a>
        </li>
        <li>
            <a href="#daorudaochu">导入导出</a>
        </li>
    </ul>
    <div class="field">
        <a name='xiugaishuju'></a>
        <h2>修改数据</h2>
        <p>update '表名' set 字段1='值1', 字段2='值2' where '条件';</p>
        <dl>
            <dt>例：</dt>
            <dd>
                <p>update 'zfuwl_test' set test1='测试1', test2='测试2' where id = 1;</p>
            </dd>
        </dl>
    </div>
    <hr>
    <div class="field">
        <a name='tianjiashuju'></a>
        <h2>添加数据（多条以逗号分开）</h2>
        <p>insert into `表名` (`字段1`,`字段2`,`字段N`) values ('值1','值2','值N');（可以添加多条）</p>
        <p>insert into `表名` (`字段1`,`字段2`,`字段N`) value ('值1','值2','值N');（只能添加单条）</p>
        <dl>
            <dt>例：</dt>
            <dd>
                <p>insert into `test` (`user_id`,`nickname`,`mobile`) values ('1','test1','15814203111');</p>
                <p>insert into `test` (`user_id`,`nickname`,`mobile`) values ('1','test1','15814203111'),('1','test1','15814203111');</p>
                <p>insert into `test` values (`1`,`test1`,`15814203111`),('1','test1','15814203111');</p>
                <p>insert into `test` value (`1`,`test1`,`15814203111`);</p>
                <p>insert into `test` (`user_id`,`nickname`,`mobile`) value ('1','test1','15814203111');</p>
            </dd>
        </dl>
    </div>
    <hr>
    <div class="field">
        <a name='shanchuku'></a>
        <h2>删除数据库</h2>
        <p>drop database 数据库名;</p>
        <dl>
            <dt>例：</dt>
            <dd>
                <p>drop database test;</p>
            </dd>
        </dl>
    </div>
    <hr>
    <div class="field">
        <a name='shanchubiao'></a>
        <h2>删除数据表</h2>
        <p>drop table 表名;</p>
        <dl>
            <dt>例：</dt>
            <dd>
                <p>drop table test;</p>
            </dd>
        </dl>
    </div>
    <hr>
    <div class="field">
        <a name='qingkongbiao'></a>
        <h2>清空数据表</h2>
        <p>truncate table 表名;</p>
        <dl>
            <dt>例：</dt>
            <dd>
                <p>truncate table test;</p>
            </dd>
        </dl>
    </div>
    <hr>
    <div class="field">
        <a name='shanchushuju'></a>
        <h2>删除数据</h2>
        <p>delete from 表名 where 条件;（无条件就删除所有）</p>
        <dl>
            <dt>例：</dt>
            <dd>
                <p>delete from test where user_id = 1;</p>
                <p>delete from test;</p>
            </dd>
        </dl>
    </div>
    <hr>
    <div class="field">
        <a name='tianjiaziduan'></a>
        <h2>添加字段（多个以逗号分开）</h2>
        <p>alter table `表名` add column `字段名` 属性 comment '备注' after `哪个字段后`;</p>
        <dl>
            <dt>例：</dt>
            <dd>
                <p>alter table `zfuwl_users_day` add column `user_a` decimal(10,0) null default '0' comment '会员a区奖金' after `team_num`;</p>
                <p>alter table `zfuwl_users_day` add column `user_a` decimal(10,0) null default '0' comment '会员a区奖金' after `team_num`,add column `user_b` decimal(10,0) null default '0' comment '会员b区奖金' after `user_a`;</p>
            </dd>
        </dl>
    </div>
    <hr>
    <div class="field">
        <a name='xiugaiziduan'></a>
        <h2>修改字段（多个以逗号分开）</h2>
        <h3>modify 和 change 区别(change需要每次重命名)</h3>
        <p>alter table `表名` change column `字段名` `要修改的字段名` 修改的属性 comment '备注' after `哪个字段后`;</p>
        <p>alter table `表名` change column `字段名` 修改的属性 comment '备注' after `哪个字段后`;</p>
        <dl>
            <dt>例(change)：</dt>
            <dd>
                <p>alter table `zfuwl_users_money_log1` change column `test12` `test12` double null default '0' comment '测试' after `admin_id`;</p>
                <p>alter table `zfuwl_users_money_log1` change column `admin_id` `admin_id` double null default '0' comment '管理员id' after `note`,change column `test12` `test12` decimal(10,0) null default '0' comment '测试' after `admin_id`;</p>
            </dd>
            <dt>例(modify)：</dt>
            <dd>
                <p>alter table `zfuwl_users_money_log1` modify column `test12` double null default '0' comment '测试' after `admin_id`;</p>
                <p>alter table `zfuwl_users_money_log1` modify column `admin_id` double null default '0' comment '管理员id' after `note`,modify column `test12` decimal(10,0) null default '0' comment '测试' after `admin_id`;</p>
            </dd>
        </dl>
    </div>
    <hr>
    <div class="field">
        <a name='chaxunjiekou'></a>
        <h2>查询数据库字段信息</h2>
        <h3>字段名和字段注释</h3>
        <p>select column_name,column_comment from information_schema.columns where table_name='表名' and table_schema='数据库名';</p>
        <h3>字段结构</h3>
        <p>desc 表名</p>
        <dl>
            <dt>例(字段名和字段注释)：</dt>
            <dd>
                <p>select column_name,column_comment from information_schema.columns where table_name='zfuwl_users_level' and table_schema='a006_jiafuw';</p>
                <p>select column_name,column_comment from information_schema.columns where table_name='zfuwl_users_level';</p>
                <p>select column_name,ordinal_position,column_default,is_nullable,column_type,column_key,extra,column_comment from information_schema.columns where table_name='zfuwl_users_car' and table_schema='bc002_jiafuw';</p>
            </dd>
            <dt>例(字段结构)：</dt>
            <dd>
                <p>desc zfuwl_users_level</p>
            </dd>
        </dl>
    </div>
    <hr>
    <div class="field">
        <a name='chaxunshuju'></a>
        <h2>查询数据</h2>
        <p>select 要查询的字段(*代表所有,多个字段以逗号隔开) from 表名 where 条件;</p>
        <p>--order by 排序</p>
        <p>--group by 分组</p>
        <p>--sum 统计所有字段值</p>
        <p>--count 统计数据数</p>
        <dl>
            <dt>例：</dt>
            <dd>
                <p>select * from test where user_id=1;</p>
                <p>select * from test;</p>
                <p>select user_id,nickname from test;</p>
            </dd>
            <dt>例(order by（desc倒序、asc正序(默认)）)：</dt>
            <dd>
                <p>select * from test where user_id=1 user_id order by desc;</p>
                <p>select * from test where user_id=1 user_id order by;</p>
            </dd>
            <dt>例(group by)：</dt>
            <dd>
                <p>select * from test where user_id=1 user_id group by user_id;</p>
            </dd>
            <dt>例(sum)：</dt>
            <dd>
                <p>select sum(user_id) from test where sum(user_id) > 1;</p>
            </dd>
            <dt>例(count)：</dt>
            <dd>
                <p>select count(*) from test;</p>
            </dd>
        </dl>
    </div>
    <hr>
    <div class="field">
        <a name='daorudaochu'></a>
        <h2>导入导出</h2>
        <h3>导出</h3>
        <p>mysqldump -u用户名 -p密码 数据库名 > 导出的文件名</p>
        <p>--all-databases 导出所有库</p>
        <p>-d 只导结构</p>
        <dl>
            <dt>例导出test数据库：</dt>
            <dd>
                <p>mysqldump -uroot -proot test > sqlfile.sql;</p>
            </dd>
            <dt>例导出test数据库中的user表和log表：</dt>
            <dd>
                <p>mysqldump -uroot -proot test user log > sqlfile.sql;</p>
            </dd>
            <dt>例(--all-databases)：</dt>
            <dd>
                <p>mysqldump -uroot -proot --all-databases > sqlfile.sql</p>
            </dd>
            <dt>例(-d)：</dt>
            <dd>
                <p>mysqldump -uroot -proot -d test > sqlfile.sql;</p>
            </dd>
        </dl>
        <h3>导入</h3>
        <p>命令进入mysql 使用source sql文件名</p>
        <dl>
            <dt>例：</dt>
            <dd>
                <p>source sqlfile.sql</p>
            </dd>
        </dl>
        <p>命令行中使用 mysql -u用户名 -p密码 数据库名 < sql文件名</p>
        <dl>
            <dt>例：</dt>
            <dd>
                <p>mysql -uroot -proot test_db < test_db.sql</p>
            </dd>
        </dl>
    </div>
</body>
</html>